﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using System.Data;
using System.Data.SqlClient;
using _619BJP.Models;

namespace _619BJP.Database.Article
{
    public class articleDB
    {
        private string connString = System.Configuration.ConfigurationManager.ConnectionStrings["619BJP.Data.ConnectionString"].ConnectionString;

        /// <summary>
        /// 百科详情
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ArticleModel articleDetail(int id)
        {
            ArticleModel article = new ArticleModel();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                addViewCount(id);

                string sql = "SELECT a.id,a.title,a.category_id,a.title,a.content,a.view_count,a.created_time,ac.category_name,ad.name FROM [AR_Article] as a "+
                            "left join AR_ArticleCategory as ac "+
                            "on a.category_id = ac.category_id "+
                            "left join SY_Admin as ad "+
                            "on a.created_by = ad.ID where a.id = " + id;
                SqlDataReader sdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sql);
                if (sdr.Read())
                {
                    article.ArticleID = Convert.ToInt32(sdr["id"].ToString());
                    article.title = sdr["title"].ToString();
                    article.content = sdr["content"].ToString();
                    article.view_count = int.Parse(sdr["view_count"].ToString());
                    article.created_name = sdr["name"].ToString();
                    article.created_time = Convert.ToDateTime(sdr["created_time"]);
                    article.category_id = Convert.ToInt32(sdr["category_id"].ToString());
                    article.category_name = sdr["category_name"].ToString();
                }
            }

            return article;

        }

        /// <summary>
        /// 上一篇
        /// </summary>
        /// <param name="currentID">当前ID</param>
        /// <returns></returns>
        public ArticleModel preArticle(int currentID)
        {
            ArticleModel article = new ArticleModel();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                string sql = "SELECT top 1 id,title FROM [AR_Article] where id < " + currentID +" order by id desc";
                SqlDataReader sdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sql);
                if (sdr.Read())
                {
                    article.ArticleID = Convert.ToInt32(sdr["id"].ToString());
                    article.title = "上一篇：" + sdr["title"].ToString();
                }
                else
                {
                    article.ArticleID = 0;
                    article.title = "已经是第一篇了";
                }
            }
            return article;
        }

        /// <summary>
        /// 下一篇
        /// </summary>
        /// <param name="currentID">当前ID</param>
        /// <returns></returns>
        public ArticleModel nextArticle(int currentID)
        {
            ArticleModel article = new ArticleModel();
            using (SqlConnection conn = new SqlConnection(connString))
            {
                string sql = "SELECT top 1 id,title FROM [AR_Article] where id > " + currentID + " order by id";
                SqlDataReader sdr = SqlHelper.ExecuteReader(conn, CommandType.Text, sql);
                if (sdr.Read())
                {
                    article.ArticleID = Convert.ToInt32(sdr["id"].ToString());
                    article.title = "下一篇：" + sdr["title"].ToString();
                }
                else
                {
                    article.ArticleID = 0;
                    article.title = "已经是最后一篇了";
                }
            }
            return article;
        }
        
        /// <summary>
        /// 添加百科查看次数
        /// </summary>
        /// <param name="id"></param>
        public void addViewCount(int id)
        {
            int count = 0;
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "SELECT view_count FROM [AR_Article] where id = " + id;
                count = Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql));

                int newcount = count + 1;
                sql = "update [AR_Article] set view_count = " + newcount + " where id = " + id;
                Convert.ToInt32(SqlHelper.ExecuteScalar(conn, CommandType.Text, sql));
            }
        }

        /// <summary>
        /// 今日焦点
        /// </summary>
        /// <returns></returns>
        public DataSet GetDailyArticle()
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //sql = "SELECT top 10 id,title FROM AR_Article WHERE created_time > DateAdd(d,-1,GetDate()) ORDER BY view_count DESC";
                sql = "SELECT top 10 id,title FROM AR_Article ORDER BY created_time DESC";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        /// 本周最热
        /// </summary>
        /// <returns></returns>
        public DataSet GetWeeklyArticle()
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //sql = "SELECT top 10 id,title FROM AR_Article WHERE created_time > DateAdd(d,-7,GetDate()) ORDER BY view_count DESC";
                sql = "SELECT top 10 id,title FROM AR_Article ORDER BY view_count DESC";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        /// 最热文章
        /// </summary>
        /// <returns></returns>
        public DataSet GetHot()
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "SELECT top 10 id,title FROM AR_Article ORDER BY view_count DESC";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        /// 最新文章
        /// </summary>
        /// <returns></returns>
        public DataSet GetNew()
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "SELECT top 10 id,title FROM AR_Article ORDER BY created_time DESC";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        /// 百科类别列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetArticleCategory()
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "SELECT * FROM AR_ArticleCategory ORDER BY category_id";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

        /// <summary>
        /// 具体类别下的文章
        /// </summary>
        /// <returns></returns>
        public DataSet GetArticleByCategory(int category_id)
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                sql = "SELECT id,title,category_id,created_time FROM AR_Article WHERE category_id = " + category_id + " ORDER BY created_time DESC";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }
        
        /// <summary>
        /// 根据关键字搜索
        /// </summary>
        /// <param name="keyWord"></param>
        /// <returns></returns>
        public DataSet GetArticleByKeyword(string keyWord)
        {
            DataSet ds = new DataSet();
            string sql = "";
            using (SqlConnection conn = new SqlConnection(connString))
            {
                //sql = "SELECT id,title,category_id,created_time FROM AR_Article WHERE CONTAINS ((title,content),'" + keyWord + "')";

                sql = "SELECT id,title,category_id,created_time FROM AR_Article WHERE title like '%" + keyWord + "%' or content like '%" + keyWord + "%' ";
                ds = SqlHelper.ExecuteDataset(conn, CommandType.Text, sql);
            }
            return ds;
        }

    }
}
